﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using ForkCarRelationManager.db;
namespace ForkCarRelationManager.Business
{
    class MaintenBI
    {
        DataPaging MaintenPage;
        DataSet MaintenDataSet;
        dbOperate dbo = new dbOperate();
        //构造函数
        public DataPaging GetMaintenpage(int pagecount)
        {
            MaintenPage = new DataPaging("ClientInfor a,MaintenRecord b,CarInfor c,ScompanyInfor d", pagecount, "(select sum(1) from MaintenRecord  where ID<=b.ID )  as 序号,a.ClientName,c.DeviceNum,d.ScompanyName,iif(b.Ifmaintain=1,'保养',iif(b.Ifmaintain=0,'维修')) as 维修类型,b.Symptom,b.MaintenContent,b.MaintenDate,b.MaintorName,iif(b.Ifrevisit=1,'已回访',iif(b.Ifrevisit=0,'未回访')) as Ifrevisit,b.ID as ID", "a.ScompanyId=d.ID and b.CarId=c.ID and c.ClientId=a.ID ", "b");
            return MaintenPage;
        }
        /*
        * 获取总数据量
        * */
        public int GetDatacount(DataPaging datapage)
        {
            int datacount=0;
            if (datapage != null)
            {
                 datacount = datapage.Getcount();
            }
            return datacount;
        }
        /*
        * 获取公司数据
        * */
        public DataTable Getallscompany()
        {
            String sqlCompany = "select s.ID,s.ScompanyName from ScompanyInfor as s";
            DataTable companyTable = dbo.GetDate(sqlCompany);
            if (companyTable == null)
            {
                companyTable = new DataTable();
                companyTable.Columns.Add("ID");
                companyTable.Columns.Add("ScompanyName");
                companyTable.Rows.Add("", "");
            }
            return companyTable;
        }
        /*
         * 更新回访情况
         * */
        public bool updateifrevisitbyid(int maintenID)
        {
            string sql = "update MaintenRecord set Ifrevisit=1 where ID=" + maintenID;
            return dbo.UpdateAndInsert(sql);
        }
        /*
         * 根据公司id获取维修人员信息
         * */
        public DataTable Getmaintorbyscid(string id)
        {
            DataTable maintorTable = null;

            string sqlmaintor = "select s.ID,s.MaintorName from MaintorInfor as s left join ScompanyInfor as p on s.ScompanyId=p.ID where s.ScompanyId=" + id;
            maintorTable = dbo.GetDate(sqlmaintor);
            if (maintorTable == null)
            {
                maintorTable = new DataTable();
                maintorTable.Columns.Add("ID");
                maintorTable.Columns.Add("maintorName");
               
            }

            
            
            return maintorTable;
        }
        /*
         * 查找所有维修人员信息
         * */
        public DataTable Getallmaintor()
        {
            DataTable maintorTable = null;

            string sqlmaintor = "select s.ID,s.MaintorName from MaintorInfor as s";
            maintorTable = dbo.GetDate(sqlmaintor);
            if (maintorTable == null)
            {
                maintorTable = new DataTable();
                maintorTable.Columns.Add("ID");
                maintorTable.Columns.Add("maintorName");
                
               
            }

            
            
            return maintorTable;
        }
        /*
         * 查找所有维修人员信息
         * */
        public DataTable Getmaintorbyname(string name)
        {
            DataTable maintorTable = null;

            string sqlmaintor = "select s.ID,s.MaintorName from MaintorInfor as s where s.MaintorName Like '%"+name+"%'";
            maintorTable = dbo.GetDate(sqlmaintor);
            if (maintorTable == null)
            {
                maintorTable = new DataTable();
                maintorTable.Columns.Add("ID");
                maintorTable.Columns.Add("maintorName");


            }



            return maintorTable;
        }
        /*
        * 获取客户数据
        * */
        public DataTable Getallclient()
        {
            String sqlClient = "select a.ID,a.ClientName from ClientInfor as a";
            DataTable clientTable = dbo.GetDate(sqlClient);
            if (clientTable == null)
            {
                clientTable = new DataTable();
                clientTable.Columns.Add("ID");
                clientTable.Columns.Add("ClientName");
                clientTable.Rows.Add("", "");
            }
            return clientTable;
        }
        /*
        * 根据客户名字模糊返回datatable
        * */
        public DataTable SearchclientIDbyname(string name)
        {
            DataTable clientTable = null;
        
                
            clientTable = dbo.GetDate("select a.ID,a.ClientName from ClientInfor as a where a.ClientName like '%" + name + "%'");
            return clientTable;
        
        }
        /*
        * 模糊查询
        * */
        public DataPaging fuzzysearch(int pagecount, string condition)
        {
            string conditionsql = "";
            string filedconditionsql = "";
            if (condition.Trim() != "" && condition != null)
            {
                conditionsql += " and (a.ClientName Like '%" + condition + "%' ";
                filedconditionsql += " and (a.ClientName Like '%" + condition + "%' ";
                conditionsql += " or  c.DeviceNum Like '%" + condition + "%' ";
                filedconditionsql += " or  c.DeviceNum Like '%" + condition + "%' ";
                conditionsql += " or  b.Symptom Like '%" + condition + "%' ";
                filedconditionsql += " or  g.Symptom Like '%" + condition + "%' ";
                conditionsql += " or  b.MaintenContent Like '%" + condition + "%') ";
                filedconditionsql += " or  g.MaintenContent Like '%" + condition + "%') ";
               
            }

            MaintenPage = new DataPaging("ClientInfor a,MaintenRecord b,CarInfor c,ScompanyInfor d", pagecount, "(select sum(1) from (select a.ClientName,c.DeviceNum,d.ScompanyName,iif(g.Ifmaintain=1,'保养',iif(g.Ifmaintain=0,'维修')) as 维修类型,g.Symptom,g.MaintenContent,g.MaintenDate,g.MaintorName,g.ID as ID from ClientInfor a,MaintenRecord g,CarInfor c,ScompanyInfor d where  a.ScompanyId=d.ID and g.CarId=c.ID and c.ClientId=a.ID " + filedconditionsql + " order by g.id ) t where t.ID<=b.ID )   as 序号,a.ClientName,c.DeviceNum,d.ScompanyName,iif(b.Ifmaintain=1,'保养',iif(b.Ifmaintain=0,'维修')) as 维修类型,b.Symptom,b.MaintenContent,b.MaintenDate,b.MaintorName,iif(b.Ifrevisit=1,'已回访',iif(b.Ifrevisit=0,'未回访')) as Ifrevisit,b.ID as ID ", " a.ScompanyId=d.ID and b.CarId=c.ID and c.ClientId=a.ID " + conditionsql, "b");
                

            
            return MaintenPage;
        }
        /*
         * 分公司筛选
         * */
       public DataPaging SearchBycompany(int pagecount, string condition, string companyid)
        {
            string conditionsql = "";
            string filedconditionsql = "";
            if (condition.Trim() != "" && condition != null && companyid.Trim() != "0" && companyid.Trim() != null)
            {
                conditionsql += " and (a.ClientName Like '%" + condition + "%' ";
                filedconditionsql += " and (a.ClientName Like '%" + condition + "%' ";
                conditionsql += " or  c.DeviceNum Like '%" + condition + "%' ";
                filedconditionsql += " or  c.DeviceNum Like '%" + condition + "%' ";
                conditionsql += " or  b.Symptom Like '%" + condition + "%' ";
                filedconditionsql += " or  g.Symptom Like '%" + condition + "%' ";
                conditionsql += " or  b.MaintenContent Like '%" + condition + "%') ";
                filedconditionsql += " or  g.MaintenContent Like '%" + condition + "%') ";
                conditionsql += " and d.ID=" + companyid + " ";
                filedconditionsql += " and d.ID=" + companyid + " ";
            }
            else if (condition.Trim() == "" && companyid.Trim() != "0" && companyid.Trim() != null)
            {
                conditionsql += " and d.ID=" + companyid + " ";
                filedconditionsql += " and d.ID=" + companyid + " ";
            }
            MaintenPage = new DataPaging("ClientInfor a,MaintenRecord b,CarInfor c,ScompanyInfor d", pagecount, "(select sum(1) from (select a.ClientName,c.DeviceNum,d.ScompanyName,iif(g.Ifmaintain=1,'保养',iif(g.Ifmaintain=0,'维修')) as 维修类型,g.Symptom,g.MaintenContent,g.MaintenDate,g.MaintorName,g.ID as ID from ClientInfor a,MaintenRecord g,CarInfor c,ScompanyInfor d where  a.ScompanyId=d.ID and g.CarId=c.ID and c.ClientId=a.ID " + filedconditionsql + " order by g.id ) t where t.ID<=b.ID )   as 序号,a.ClientName,c.DeviceNum,d.ScompanyName,iif(b.Ifmaintain=1,'保养',iif(b.Ifmaintain=0,'维修')) as 维修类型,b.Symptom,b.MaintenContent,b.MaintenDate,b.MaintorName,iif(b.Ifrevisit=1,'已回访',iif(b.Ifrevisit=0,'未回访')) as Ifrevisit,b.ID as ID ", " a.ScompanyId=d.ID and b.CarId=c.ID and c.ClientId=a.ID " + conditionsql, "b");


            return MaintenPage;
        }
          
        public  DataPaging SearchBymaintorname(int pagecount, string condition, string maintorname,string companyid)
        {
            string conditionsql = "";
            string filedconditionsql = "";
            if (condition.Trim() != "" && condition != null && maintorname.Trim() != "" && maintorname.Trim() != null)
            {
                conditionsql += " and (a.ClientName Like '%" + condition + "%' ";
                filedconditionsql += " and (a.ClientName Like '%" + condition + "%' ";
                conditionsql += " or  c.DeviceNum Like '%" + condition + "%' ";
                filedconditionsql += " or  c.DeviceNum Like '%" + condition + "%' ";
                conditionsql += " or  b.Symptom Like '%" + condition + "%' ";
                filedconditionsql += " or  g.Symptom Like '%" + condition + "%' ";
                conditionsql += " or  b.MaintenContent Like '%" + condition + "%') ";
                filedconditionsql += " or  g.MaintenContent Like '%" + condition + "%') ";
                conditionsql += " and d.ID=" + companyid + " ";
                filedconditionsql += " and d.ID=" + companyid + " ";
                conditionsql += " and b.MaintorName Like '%" + maintorname + "%' ";
                filedconditionsql += " and g.MaintorName Like '%" + maintorname + "%' ";
            }
            else if (condition.Trim() == "" && maintorname.Trim() != "" && maintorname.Trim() != null)
            {
                conditionsql += " and d.ID=" + companyid + " ";
                filedconditionsql += " and d.ID=" + companyid + " ";
                conditionsql += " and b.MaintorName Like '%" + maintorname + "%' ";
                filedconditionsql += " and g.MaintorName Like '%" + maintorname + "%' ";
            }

            MaintenPage = new DataPaging("ClientInfor a,MaintenRecord b,CarInfor c,ScompanyInfor d", pagecount, "(select sum(1) from (select a.ClientName,c.DeviceNum,d.ScompanyName,iif(g.Ifmaintain=1,'保养',iif(g.Ifmaintain=0,'维修')) as 维修类型,g.Symptom,g.MaintenContent,g.MaintenDate,g.MaintorName,g.ID as ID from ClientInfor a,MaintenRecord g,CarInfor c,ScompanyInfor d where  a.ScompanyId=d.ID and g.CarId=c.ID and c.ClientId=a.ID " + filedconditionsql + " order by g.id ) t where t.ID<=b.ID )   as 序号,a.ClientName,c.DeviceNum,d.ScompanyName,iif(b.Ifmaintain=1,'保养',iif(b.Ifmaintain=0,'维修')) as 维修类型,b.Symptom,b.MaintenContent,b.MaintenDate,b.MaintorName,iif(b.Ifrevisit=1,'已回访',iif(b.Ifrevisit=0,'未回访')) as Ifrevisit,b.ID as ID ", "a.ScompanyId=d.ID and b.CarId=c.ID and c.ClientId=a.ID " + conditionsql, "b");


            return MaintenPage;
         }
        /*
     * 按时间筛选
     * */
        public DataPaging Searchbytime(int pagecount, string condition, string companyid, string maintorname, string begintime, string endtime)
        {
            string conditionsql = "";
            string filedconditionsql = "";
            if (condition.Trim() != "" && maintorname.Trim() != "")//都有的情况
            {
                conditionsql += " and (a.ClientName Like '%" + condition + "%' ";
                filedconditionsql += " and (a.ClientName Like '%" + condition + "%' ";
                conditionsql += " or  c.DeviceNum Like '%" + condition + "%' ";
                filedconditionsql += " or  c.DeviceNum Like '%" + condition + "%' ";
                conditionsql += " or  b.Symptom Like '%" + condition + "%' ";
                filedconditionsql += " or  g.Symptom Like '%" + condition + "%' ";
                conditionsql += " or  b.MaintenContent Like '%" + condition + "%') ";
                filedconditionsql += " or  g.MaintenContent Like '%" + condition + "%') ";
                conditionsql += " and d.ID=" + companyid + " ";
                filedconditionsql += " and d.ID=" + companyid + " ";
                conditionsql += " and b.MaintorName Like '%" + maintorname + "%' ";
                filedconditionsql += " and g.MaintorName Like '%" + maintorname + "%' ";
            }
            else if (condition.Trim() != "" && companyid.Trim() != "0" && maintorname.Trim() == "")//只有Condition和公司情况
            {
                conditionsql += " and (a.ClientName Like '%" + condition + "%' ";
                filedconditionsql += " and (a.ClientName Like '%" + condition + "%' ";
                conditionsql += " or  c.DeviceNum Like '%" + condition + "%' ";
                filedconditionsql += " or  c.DeviceNum Like '%" + condition + "%' ";
                conditionsql += " or  b.Symptom Like '%" + condition + "%' ";
                filedconditionsql += " or  g.Symptom Like '%" + condition + "%' ";
                conditionsql += " or  b.MaintenContent Like '%" + condition + "%') ";
                filedconditionsql += " or  g.MaintenContent Like '%" + condition + "%') ";
                conditionsql += " and d.ID=" + companyid + " ";
                filedconditionsql += " and d.ID=" + companyid + " ";
            }
            else if (condition.Trim() == "" && maintorname.Trim() != "")//没有condition有公司和人员的情况
            {
                conditionsql += " and d.ID=" + companyid + " ";
                filedconditionsql += " and d.ID=" + companyid + " ";
                conditionsql += " and b.MaintorName Like '%" + maintorname + "%' ";
                filedconditionsql += " and g.MaintorName Like '%" + maintorname + "%' ";
            }
            else if (condition.Trim() == "" && companyid.Trim() != "0" && maintorname.Trim() == "")//没有condition有公司没有人员情况
            {
                conditionsql += " and d.ID=" + companyid + " ";
                filedconditionsql += " and d.ID=" + companyid + " ";
            }
            else if (condition.Trim() != "" && companyid.Trim() == "0" && maintorname.Trim() == "")//只有condition情况
            {
                conditionsql += " and (a.ClientName Like '%" + condition + "%' ";
                filedconditionsql += " and (a.ClientName Like '%" + condition + "%' ";
                conditionsql += " or  c.DeviceNum Like '%" + condition + "%' ";
                filedconditionsql += " or  c.DeviceNum Like '%" + condition + "%' ";
                conditionsql += " or  b.Symptom Like '%" + condition + "%' ";
                filedconditionsql += " or  g.Symptom Like '%" + condition + "%' ";
                conditionsql += " or  b.MaintenContent Like '%" + condition + "%') ";
                filedconditionsql += " or  g.MaintenContent Like '%" + condition + "%') ";
            }
            conditionsql += " and b.MaintenDate between #" + begintime + "# and #" + endtime + "# ";
            filedconditionsql += " and g.MaintenDate between #" + begintime + "# and #" + endtime + "# ";

            MaintenPage = new DataPaging("ClientInfor a,MaintenRecord b,CarInfor c,ScompanyInfor d", pagecount, "(select sum(1) from (select a.ClientName,c.DeviceNum,d.ScompanyName,iif(g.Ifmaintain=1,'保养',iif(g.Ifmaintain=0,'维修')) as 维修类型,g.Symptom,g.MaintenContent,g.MaintenDate,g.MaintorName,g.ID as ID from ClientInfor a,MaintenRecord g,CarInfor c,ScompanyInfor d where  a.ScompanyId=d.ID and g.CarId=c.ID and c.ClientId=a.ID " + filedconditionsql + " order by g.id ) t where t.ID<=b.ID )   as 序号,a.ClientName,c.DeviceNum,d.ScompanyName,iif(b.Ifmaintain=1,'保养',iif(b.Ifmaintain=0,'维修')) as 维修类型,b.Symptom,b.MaintenContent,b.MaintenDate,b.MaintorName,iif(b.Ifrevisit=1,'已回访',iif(b.Ifrevisit=0,'未回访')) as Ifrevisit,b.ID as ID ", "a.ScompanyId=d.ID and b.CarId=c.ID and c.ClientId=a.ID " + conditionsql, "b");


            return MaintenPage;
        }
        public DataPaging Searchmainten(int pagecount,string clientName, string maintenContext, string scompanyName, string maintorName, string begindate, string enddate)
        {

            string conditionsql = "";
            string filedconditionsql = "";
            if (clientName.Trim() != "" && clientName != null)
            {
                conditionsql += " and a.ClientName Like '%" + clientName + "%' ";
                filedconditionsql += " and a.ClientName Like '%" + clientName + "%' ";
            }
            if (maintenContext.Trim() != "" && maintenContext != null)
            {
                conditionsql += " and  b.MaintenContent Like '%" + maintenContext + "%' ";
                filedconditionsql += " and  g.MaintenContent Like '%" + maintenContext + "%' ";
            }
            if (scompanyName.Trim() != "" && scompanyName != null)
            {
                conditionsql += " and d.ScompanyName Like '%" + scompanyName + "%' ";
                filedconditionsql += " and d.ScompanyName Like '%" + scompanyName + "%' ";
            }
            if (maintorName.Trim() != "" && maintorName != null)
            {
                conditionsql += " and e.MaintorName Like '%" + maintorName + "%' ";
                filedconditionsql += " and e.MaintorName Like '%" + maintorName + "%' ";
            }
            conditionsql += " and b.MaintenDate between #" + begindate + "# and #" + enddate + "# " ;
            filedconditionsql += " and g.MaintenDate between #" + begindate + "# and #" + enddate + "# ";
            MaintenPage = new DataPaging("ClientInfor a,MaintenRecord b,CarInfor c,ScompanyInfor d,MaintorInfor e", pagecount, "(select sum(1) from (select a.ClientName,c.DeviceNum,d.ScompanyName,iif(g.Ifmaintain=1,'保养',iif(g.Ifmaintain=0,'维修')) as 维修类型,g.Symptom,g.MaintenContent,g.MaintenDate,e.MaintorName,g.ID as ID from ClientInfor a,MaintenRecord g,CarInfor c,ScompanyInfor d,MaintorInfor e where g.MaintorId=e.ID and e.ScompanyId=d.ID and g.CarId=c.ID and c.ClientId=a.ID " + filedconditionsql + " order by g.id ) t where t.ID<=b.ID )   as 序号,a.ClientName,c.DeviceNum,d.ScompanyName,iif(b.Ifmaintain=1,'保养',iif(b.Ifmaintain=0,'维修')) as 维修类型,b.Symptom,b.MaintenContent,b.MaintenDate,e.MaintorName,b.ID as ID ", "b.MaintorId=e.ID and e.ScompanyId=d.ID and b.CarId=c.ID and c.ClientId=a.ID " + conditionsql, "b");
            return MaintenPage;
        }
        /*
        * 根据客户id查其所属公司id
        * */
        public int Getscompanyidbycid(string id)
        {
            DataTable clientTable = null;
            string sql = "select c.ScompanyId from ClientInfor as c where c.ID=" + id;
            clientTable = dbo.GetDate(sql);
            if (clientTable.Rows.Count != 0)
            {
                return Convert.ToInt32(clientTable.Rows[0][0].ToString());
            }
            else
                return 0;
        }
        public DataTable Getcontact(string id)
        {
            DataTable clientTable = null;
            string sql = "select c.ContactName,c.ContactPhone from ClientInfor as c where c.ID=" + id;
            clientTable = dbo.GetDate(sql);
            if (clientTable.Rows.Count != 0)
            {
                return clientTable;
            }
            else
                return clientTable;
        }
        /*
       * 根据公司id查其公司名字
       * */
        public string Getscompanynamebycid(int id)
        {
            DataTable clientTable = null;
            string sql = "select s.ScompanyName from ScompanyInfor as s where s.ID=" + id;
            clientTable = dbo.GetDate(sql);
            if (clientTable.Rows.Count != 0)
            {
                return clientTable.Rows[0][0].ToString();
            }
            else
                return "";
        }
        /*
         * 根据客户id查其车
         * */
        public DataTable Getcarbycid(string id)
        {
            DataTable carTable = null;
            string sqlmaintor = "select c.ID,c.DeviceNum from CarInfor as c where c.ClientId=" + id;
            carTable = dbo.GetDate(sqlmaintor);
            if (carTable == null)
            {
                carTable = new DataTable();
                carTable.Columns.Add("ID");
                carTable.Columns.Add("DeviceNum");
                carTable.Rows.Add("", "");
            }
            return carTable;
        }
        /*
         * 根据机号查叉车ID
         * */
        public int SearchcarIDbycnum(string devicenum)
        {
            if (devicenum != "")
            {
                DataTable carTable = null;
                carTable = dbo.GetDate("select ID from CarInfor where DeviceNum='" + devicenum + "'");
                if (carTable != null)
                {
                    if (carTable.Rows.Count!=0)
                    {
                        return Convert.ToInt32(carTable.Rows[0][0].ToString());
                    }
                    else
                        return 0;
                }
                else
                    return 0;
            }
            else
                return 0;
        }
        /*
      * 根据车辆ID查车全部信息
      * */
        public DataTable SearchcarbyID(int carID)
        {
            DataTable carTable = null;
            string sqlmaintor = "select * from CarInfor  where ID=" + carID;
            carTable = dbo.GetDate(sqlmaintor);
            if (carTable == null)
            {
                carTable = new DataTable();
                carTable.Columns.Add("ID");
                carTable.Columns.Add("CarType");
                carTable.Columns.Add("Brand");
                carTable.Columns.Add("Model");
                carTable.Columns.Add("SerialNum");

                carTable.Rows.Add("", "","","","");
          
            }
            return carTable;
        }
        /*
       * 根据维修人员名字返回其id
       * */
        public int SearchmaintorIDbyname(string name)
        {
            if (name.Trim() != "")
            {
                DataTable maintorTable = null;
                maintorTable = dbo.GetDate("select ID from MaintorInfor where MaintorName='" + name.Trim() + "'");
                string sql = "select ID from MaintorInfor where MaintorName='" + name.Trim() + "'";
                if (maintorTable != null)
                {
                    if (maintorTable.Rows.Count != 0)
                    {
                        return Convert.ToInt32(maintorTable.Rows[0][0].ToString());
                    }
                    else
                        return 0;
                    
                }
                else
                    return 0;
            }
            else
                return 0;
        }
      
        /*
         * 提交维修信息并返回维修信息id
         * */
        public int addmaintenInfor(int carId, string MaintenDate, int Ifmaintain, int Iffree, string Symptom, string MaintenContent, string MaintorName, string ClientAdvice, string BackMessage, int WorkTime, string CheckContent, string Changepart, double Fear, string Provicenum, int RepireID)
        {
            int maintenid = 0;
            bool ifaddsucs = dbo.UpdateAndInsert("insert into MaintenRecord (MaintenContent,CarId,MaintenDate,Ifmaintain,Iffree,Provicenum,Symptom,MaintorName,ClientAdvice,BackMessage,WorkTime,CheckContent,Changepart,Fear,Ifrevisit,RepireID) values ('" + MaintenContent + "'," + carId + ",#" + MaintenDate + "#," + Ifmaintain + "," + Iffree + ",'" + Provicenum + "','" + Symptom + "','" + MaintorName + "','" + ClientAdvice + "','" + BackMessage + "'," + WorkTime + ",'" + CheckContent + "','" + Changepart + "'," + Fear + ",0," + RepireID + ")");
            if (ifaddsucs)
            {
                DataTable maintenTable = null;
                maintenTable = dbo.GetDate("select Id from MaintenRecord where MaintenContent='" + MaintenContent + "' and CarId=" + carId + "and MaintenDate = #" + MaintenDate + "# and Ifmaintain=" + Ifmaintain + " and Iffree =" + Iffree + " and Symptom ='" + Symptom + "' and MaintorName ='" + MaintorName + "' and WorkTime=" + WorkTime + " order by Id desc");
                string sql = "select Id from MaintenRecord where MaintenContent='" + MaintenContent + "' and CarId=" + carId + "and MaintenDate = #" + MaintenDate + "# and Ifmaintain=" + Ifmaintain + " and Iffree =" + Iffree + " and Symptom ='" + Symptom + "' and MaintorName ='" + MaintorName + "' and WorkTime=" + WorkTime;
                if (maintenTable != null)
                {
                    maintenid = Convert.ToInt32( maintenTable.Rows[0][0].ToString());
                }
                else
                    maintenid = 0;
            }
            return maintenid;
        }
        /*
        * 根据客户姓名查其ID
        * */
        public int getclientIDbyname(string name)
        {
            int clientID = 0;
            string sql = "select ID from ClientInfor where ClientName='" + name + "'";
            DataTable clientTable = dbo.GetDate(sql);


            if (clientTable.Rows.Count!=0)
                {
                    clientID = Convert.ToInt32(clientTable.Rows[0][0].ToString());
                }
                else
                clientID = 0;
            return clientID;
            
        }
          /*
        * 根据维修记录id删除维修信息
        * */
        public bool deletmaintenbyid(int maintenid)
        {
            return dbo.Delete("delete from MaintenRecord where ID=" + maintenid);
        }
        /*
        * 根据维修记录id提交零件使用信息
        * */
        public bool addmaintenpartInfor(string PartName, string Standard, int partCount, string Unit, double UnitPrice, double Price, string Remark,int maintenid)
        {
            string sql="insert into UsePartsinfor (PartName,Standard,partCount,Unit,UnitPrice,Price,Remark,MaintenId) values ('" + PartName + "','" + Standard + "'," + partCount + ",'" + Unit + "'," + UnitPrice + "," + Price + ",'" + Remark + "'," + maintenid +  ")";
            bool ifok=  dbo.UpdateAndInsert(sql);
            return ifok;
        }
        /*
         * 根据维修id查看详情
         * */
        public DataTable GetmaintenDetailsbyid(int maintenid)
        {
            DataTable maintenDetaiTable = null;
            string sqlmaintor = "select a.ClientName,a.ContactName,a.ContactPhone,c.DeviceNum,c.CarType,c.Brand,c.Model,c.SerialNum,c.SumHour,b.Ifmaintain,b.Iffree,b.Provicenum,b.MaintorName,b.WorkTime,b.Ifrevisit,b.RepireID,b.Fear,b.CheckContent,b.Changepart,b.MaintenDate,b.Symptom,b.MaintenContent,b.ClientAdvice,b.BackMessage from ClientInfor a,MaintenRecord b,CarInfor c where   b.CarId=c.ID and c.ClientId=a.ID and b.ID=" + maintenid;
            maintenDetaiTable = dbo.GetDate(sqlmaintor);

            return maintenDetaiTable;
        }
        /*
        * 根据维修id查看零件使用详情
        * */
        public DataTable GetpartDetailsbyid(int maintenid)
        {
            DataTable partsDetaiTable = null;
            string sqlmaintor = "select PartName,Standard,partCount,Unit,UnitPrice,Price,Remark,ID from UsePartsinfor  where MaintenId=" + maintenid;
            partsDetaiTable = dbo.GetDate(sqlmaintor);
            if (partsDetaiTable == null)
            {
                partsDetaiTable = new DataTable();
                partsDetaiTable.Columns.Add("ID");
                partsDetaiTable.Columns.Add("PartName");
                partsDetaiTable.Columns.Add("Standard");
                partsDetaiTable.Columns.Add("partCount");
                partsDetaiTable.Columns.Add("Unit");
                partsDetaiTable.Columns.Add("UnitPrice");
                partsDetaiTable.Columns.Add("Price");
                partsDetaiTable.Columns.Add("Remark");
                partsDetaiTable.Rows.Add("","","","","","","","");
            }
            return partsDetaiTable;
        }
        /*
         * 修改维修text信息
         * */
        public bool updatemaintenRecordbyid(int maintenID, int Ifmaintain, int Iffree, string Provicenum, string Symptom, string MaintenContent, string ClientAdvice, string BackMessage, int WorkTime, string checkContext, string changepart)
        {
            string sql = "Update MaintenRecord Set Ifmaintain=" + Ifmaintain + ",Iffree=" + Iffree + ",Provicenum='" + Provicenum + "',Symptom='" + Symptom + "',MaintenContent='" + MaintenContent + "',ClientAdvice='" + ClientAdvice + "',BackMessage='" + BackMessage + "',WorkTime=" + WorkTime + ",CheckContent='" + checkContext + "',Changepart='" + changepart + "' where ID=" + maintenID;
            return dbo.UpdateAndInsert(sql);

        }
        /*
     * 修改零件信息
     * */
        public bool updatepartbyid(int partID, string PartName, string Standard, int partCount, string Unit, double UnitPrice, double Price, string Remark)
        {
            string sql = "Update UsePartsinfor Set PartName='" + PartName + "',Standard='" + Standard + "',partCount=" + partCount + ",Unit='" + Standard + "',UnitPrice=" + UnitPrice + ",Price=" + Price + ",Remark='" + Remark + "' where ID=" + partID;
            return dbo.UpdateAndInsert(sql);
        }
        /*
        * 删除
        * */
        public bool deletepartbyid(int partID)
        {
            string sql = "Delete From UsePartsinfor where ID=" + partID;
            return dbo.UpdateAndInsert(sql);
        }
        /*
         * 更新fear
         * */
        public bool updatefearByid(int maintenID,double fear)
        {
            string sql = "Update MaintenRecord Set Fear=" + fear + " where ID=" + maintenID;
            return dbo.UpdateAndInsert(sql);
        }
        /*
         * 更新车辆总工作时间
         * */
        public bool updatecarSumhour(int carId, int SumHour)
        {
            string sql = "Update CarInfor Set SumHour=" + SumHour + " where ID=" + carId;
            return dbo.UpdateAndInsert(sql);
        }
        /*
         * 添加工作量表 名字分隔符为,
         * */
        public bool addworkload(string maintorname, double money, int worktime,int maintenID )
        {
            bool mark = false;
            string[] nameArr = maintorname.Split(new char[] { ',' });
            double Avemoney = money / nameArr.Count();
            Avemoney = Math.Round(Avemoney, 2);
            double Aveworktime = Convert.ToDouble(worktime) / Convert.ToDouble(nameArr.Count());
            Aveworktime = Math.Round(Aveworktime, 2);
            int maintorID=0;
            foreach(string name in nameArr )
            {
                maintorID = SearchmaintorIDbyname(name);
                if (maintorID != 0)
                {
                    string sql = " insert into Workload (MaintorID,MaintenID,Wmoney,Worktime) values (" + maintorID + "," + maintenID + "," + Math.Round(Avemoney, 2) + "," + Math.Round(Aveworktime, 2) + ")";
                    mark= dbo.UpdateAndInsert(sql);
                    if (!mark)
                    {
                        break;
                    }
                }
            
            }
            return mark;
            
        }
        /*
        * 更新工作量表 名字分隔符为,
        * */
        public bool updateworkload(string maintorname, double money, int worktime, int maintenID)
        {
            bool mark = false;
            string[] nameArr = maintorname.Split(new char[] { ',' });
            double Avemoney = money / nameArr.Count();
            Avemoney = Math.Round(Avemoney, 2);
            double Aveworktime = Convert.ToDouble(worktime) / Convert.ToDouble(nameArr.Count());
            Aveworktime = Math.Round(Aveworktime, 2);
            string sql = " update  Workload set Worktime =" + Aveworktime + ",Wmoney=" + Avemoney + " where MaintenID=" + maintenID;
            mark = dbo.UpdateAndInsert(sql);
          /*  int maintorID = 0;
            foreach (string name in nameArr)
            {
                maintorID = SearchmaintorIDbyname(name);
                if (maintorID != 0)
                {
                    string sql = " update  Workload set Worktime =" + Aveworktime + ",Wmoney=" + Avemoney + " where MaintenID=" + maintenID ;
                    mark = dbo.UpdateAndInsert(sql);
                    if (!mark)
                    {
                        break;
                    }
                }

            }*/
            return mark;

        }
       
    }
}
 